from typing import List, Dict, Optional

from openpyxl import Workbook
from openpyxl.cell import Cell
from openpyxl.styles import PatternFill, Alignment, Font, Border, Side
from openpyxl.utils import get_column_letter
from openpyxl.worksheet.worksheet import Worksheet
from pydantic import BaseModel

from .schema import (
    SummaryTable,
    SummaryProduct,
    Offer,
    OfferDetail,
    ExceptionOffer,
    ExceptionOfferDetail,
)

# 样式定义保持不变
YELLOW_FILL = PatternFill(fill_type="solid", fgColor="FFFF00")
GRAY_FILL = PatternFill(fill_type="solid", fgColor="D3D3D3")
RED_FILL = PatternFill(fill_type="solid", fgColor="FA8072")
ALIGNMENT = Alignment(horizontal="center", vertical="center")
SIDE = Side(border_style="thin", color="000000")
BORDER = Border(top=SIDE, bottom=SIDE, left=SIDE, right=SIDE)

# 新表头列定义
COL_CATEGORY = 1  # A列：类别
COL_CODE = 2  # B列：存货编码
COL_NAME = 3  # C列：存货名称
COL_SPEC = 4  # D列：规格型号
COL_UNIT = 5  # E列：单位
COL_QUANTITY = 6  # F列：采购申请数量
COL_PRICE = 7  # G列：报价
COL_TOTAL = 8  # H列：总价
COL_REMARK = 9  # I列：备注（品牌）


def set_common_style(cell):
    """设置通用单元格样式"""
    cell.font = Font(name="宋体", size=10, bold=False)
    cell.alignment = ALIGNMENT
    cell.border = BORDER


def set_header_style(cell: Cell):
    """设置表头样式"""
    set_common_style(cell)
    cell.font = Font(name="黑体", size=10, bold=True)


def set_lowest_price_style(cell: Cell):
    """设置最低价样式"""
    set_common_style(cell)
    cell.fill = YELLOW_FILL


def set_exception_style(cell: Cell, status: int):
    """设置异常单元格样式"""
    set_common_style(cell)
    if status == -1:
        cell.fill = RED_FILL
    elif status == -2:
        cell.fill = GRAY_FILL


class SupplierColumns(BaseModel):
    """供应商列配置"""

    supplier: str
    start_col: int
    has_bargaining: bool
    price_col: int  # 单价列
    bargain_col: Optional[int] = None  # 二次议价列（可选）
    total_col: int  # 总价列
    remark_col: int  # 备注列


class Exporter:
    def __init__(self, tables: List[SummaryTable]):
        self.tables = tables
        self.workbook = Workbook()

    def export(self, file_path: str):
        """导出Excel主方法"""
        sheet_names = self._create_worksheets()

        for sheet_name in sheet_names:
            table = next((t for t in self.tables if t.table_name == sheet_name), None)
            if not table:
                continue

            sheet = self.workbook[sheet_name]

            # 1. 设置表头
            supplier_cols, lowest_col = self._set_new_header(sheet, table)

            # 2. 填充正常产品数据
            self._fill_data(sheet, table)

            # 3. 填充异常产品数据
            if hasattr(table, "exceptions") and table.exceptions:
                self._fill_exception_products(
                    sheet, table, sheet.max_row, supplier_cols
                )

            # 4. 应用样式和调整列宽
            self._apply_styles(sheet)
            self._adjust_column_widths(sheet)

        self.workbook.save(file_path)

    def _create_worksheets(self):
        """创建工作表"""
        for table in self.tables:
            self.workbook.create_sheet(title=table.table_name)

        if "Sheet" in self.workbook.sheetnames:
            self.workbook.remove(self.workbook["Sheet"])

        return self.workbook.sheetnames

    def _set_new_header(self, sheet: Worksheet, table: SummaryTable):
        """设置新表头结构，确保始终返回(supplier_cols, lowest_col)"""
        # 基础产品信息列
        headers = ["类别", "存货编码", "存货名称", "规格型号", "单位", "采购申请数量"]
        for col, header in enumerate(headers, start=1):
            cell = sheet.cell(row=1, column=col, value=header)
            set_header_style(cell)

        # 供应商报价列（从第7列开始）
        col_idx = 7
        supplier_cols = {}

        for supplier in table.suppliers:
            has_bargaining = supplier in table.suppliers_with_bargaining
            start_col = col_idx

            # 确定列结构
            price_col = start_col
            bargain_col = start_col + 1 if has_bargaining else None
            total_col = start_col + (2 if has_bargaining else 1)
            remark_col = start_col + (3 if has_bargaining else 2)
            end_col = remark_col

            # 供应商名称合并单元格
            sheet.merge_cells(
                start_row=1, end_row=1, start_column=start_col, end_column=end_col
            )
            sheet.cell(row=1, column=start_col, value=supplier)

            # 写入子标题
            titles = ["单价"]
            if has_bargaining:
                titles.append("二次议价")
            titles.extend(["总价", "备注"])

            for i, title in enumerate(titles):
                sheet.cell(row=2, column=start_col + i, value=title)
                set_header_style(sheet.cell(row=2, column=start_col + i))

            # 创建列配置对象
            supplier_cols[supplier] = SupplierColumns(
                supplier=supplier,
                start_col=start_col,
                has_bargaining=has_bargaining,
                price_col=price_col,
                bargain_col=bargain_col,
                total_col=total_col,
                remark_col=remark_col,
            )

            col_idx = end_col + 1

        # 最低价列
        lowest_col = col_idx
        sheet.cell(row=1, column=lowest_col, value="最低价")
        sheet.merge_cells(
            start_row=1, end_row=2, start_column=lowest_col, end_column=lowest_col
        )
        set_header_style(sheet.cell(row=1, column=lowest_col))

        # 价差列
        for i, supplier in enumerate(table.suppliers):
            diff_col = lowest_col + 1 + i
            sheet.cell(row=1, column=diff_col, value=f"{supplier}-价差")
            sheet.merge_cells(
                start_row=1, end_row=2, start_column=diff_col, end_column=diff_col
            )
            set_header_style(sheet.cell(row=1, column=diff_col))

        # 确保始终返回两个值
        return supplier_cols, lowest_col

    def _fill_offer_cell(self, sheet, row, col, offer):
        """填充报价单元格"""
        cell = sheet.cell(row=row, column=col)

        if isinstance(offer, ExceptionOffer):
            cell.value = offer.detail.unit_price
            set_exception_style(cell, offer.status)
        else:
            cell.value = offer.detail.unit_price
            set_common_style(cell)

    def _fill_total_price(self, sheet, row, col, offer, product_detail):
        """填充总价单元格（确保正确计算）"""
        cell = sheet.cell(row=row, column=col)

        if isinstance(offer, ExceptionOffer):
            cell.value = "N/A"
            set_exception_style(cell, offer.status)
            return

        try:
            # 获取数量和单价
            quantity = float(product_detail.quantity or 0)
            unit_price = float(offer.detail.unit_price or 0)

            # 计算总价
            total_price = quantity * unit_price
            cell.value = round(total_price, 2)
            set_common_style(cell)
        except (ValueError, TypeError):
            cell.value = "计算错误"
            set_exception_style(cell, -1)

    def _fill_data(self, sheet: Worksheet, table: SummaryTable):
        """填充数据内容"""
        supplier_cols, lowest_col = self._set_new_header(sheet, table)
        row_idx = 3  # 数据从第3行开始

        for product in table.products:
            detail = product.detail

            # 填充基础产品信息（A-F列）
            sheet.cell(row=row_idx, column=COL_CATEGORY, value="钢材")
            sheet.cell(row=row_idx, column=COL_CODE, value=detail.code)
            sheet.cell(row=row_idx, column=COL_NAME, value=detail.name)
            sheet.cell(row=row_idx, column=COL_SPEC, value=detail.spec)
            sheet.cell(row=row_idx, column=COL_UNIT, value=detail.unit)
            sheet.cell(row=row_idx, column=COL_QUANTITY, value=detail.quantity)

            # 初始化报价映射
            offer_map = {}
            for offer in product.offers:
                supplier = offer.supplier
                if supplier not in offer_map:
                    offer_map[supplier] = {"initial": None, "bargaining": None}

                if offer.is_bargaining:
                    offer_map[supplier]["bargaining"] = offer
                else:
                    offer_map[supplier]["initial"] = offer

            # 填充供应商报价
            for supplier, config in supplier_cols.items():
                if supplier not in offer_map:
                    continue

                offers = offer_map[supplier]

                # 填充初轮报价（单价列）
                if offers["initial"]:
                    self._fill_offer_cell(
                        sheet, row_idx, config.price_col, offers["initial"]
                    )
                    # 计算并填充总价
                    self._fill_total_price(
                        sheet, row_idx, config.total_col, offers["initial"], detail
                    )

                # 填充二次议价（如有）
                if config.has_bargaining and offers["bargaining"]:
                    self._fill_offer_cell(
                        sheet, row_idx, config.bargain_col, offers["bargaining"]
                    )
                    # 计算并填充总价（使用二次议价价格）
                    self._fill_total_price(
                        sheet, row_idx, config.total_col, offers["bargaining"], detail
                    )

                # 填充备注
                final_offer = offers["bargaining"] or offers["initial"]
                if final_offer:
                    sheet.cell(
                        row=row_idx,
                        column=config.remark_col,
                        value=getattr(final_offer.detail, "remark", ""),
                    )

            # 计算最低价和价差
            self._fill_lowest_price(
                sheet, row_idx, offer_map, supplier_cols, lowest_col
            )
            row_idx += 1

    def _fill_lowest_price(self, sheet, row, offer_map, supplier_cols, lowest_col):
        """填充最低价和价差信息"""
        valid_offers = []

        # 收集所有有效报价
        for supplier, offers in offer_map.items():
            final_offer = offers["bargaining"] or offers["initial"]
            if final_offer and isinstance(final_offer, Offer):
                valid_offers.append((supplier, final_offer))

        if not valid_offers:
            sheet.cell(row=row, column=lowest_col, value="无有效报价")
            return

        # 计算最低价
        min_price = min(offer.key_price for _, offer in valid_offers)
        sheet.cell(row=row, column=lowest_col, value=min_price)

        # 标记最低价供应商
        min_suppliers = [
            supplier
            for supplier, offer in valid_offers
            if abs(offer.key_price - min_price) < 1e-5
        ]

        # 填充价差并高亮最低价
        for i, (supplier, offer) in enumerate(valid_offers):
            diff = offer.key_price - min_price
            diff_col = lowest_col + 1 + i
            sheet.cell(row=row, column=diff_col, value=diff)

            # 高亮最低价
            if supplier in min_suppliers:
                config = supplier_cols[supplier]
                price_col = config.start_col + (1 if offer.is_bargaining else 0)
                set_lowest_price_style(sheet.cell(row=row, column=price_col))

    def _fill_exception_products(self, sheet, table, start_row, supplier_cols):
        """填充异常产品信息"""
        if not hasattr(table, "exceptions") or not table.exceptions:
            return

        row_idx = start_row + 2  # 空两行作为分隔

        for exception in table.exceptions:
            detail = exception.detail

            # 填充基础信息（A-E列）
            sheet.cell(row=row_idx, column=COL_CATEGORY, value="")
            sheet.cell(row=row_idx, column=COL_CODE, value=detail.code)
            sheet.cell(row=row_idx, column=COL_NAME, value=detail.name)
            sheet.cell(row=row_idx, column=COL_SPEC, value=detail.spec)
            sheet.cell(row=row_idx, column=COL_UNIT, value=detail.unit)

            # 标记为异常行（灰色背景）
            for col in range(1, 6):  # A-E列
                set_exception_style(sheet.cell(row=row_idx, column=col), status=-2)

            # 填充异常报价信息（如果有）
            if exception.offer and supplier_cols:
                supplier = exception.offer.supplier
                if supplier in supplier_cols:
                    config = supplier_cols[supplier]

                    # 填充单价
                    sheet.cell(
                        row=row_idx,
                        column=config.price_col,
                        value=exception.offer.detail.unit_price,
                    )
                    set_exception_style(
                        sheet.cell(row=row_idx, column=config.price_col),
                        status=exception.offer.status,
                    )

                    # 填充总价（标记为N/A）
                    sheet.cell(row=row_idx, column=config.total_col, value="N/A")
                    set_exception_style(
                        sheet.cell(row=row_idx, column=config.total_col),
                        status=exception.offer.status,
                    )

                    # 填充备注
                    sheet.cell(
                        row=row_idx,
                        column=config.remark_col,
                        value=getattr(exception.offer.detail, "remark", "异常产品"),
                    )

            row_idx += 1

    def _apply_styles(self, sheet: Worksheet):
        """应用通用样式"""
        for row in sheet.iter_rows():
            for cell in row:
                set_common_style(cell)

    def _adjust_column_widths(self, sheet: Worksheet):
        """调整列宽"""
        widths = {
            "A": 8,  # 类别
            "B": 12,  # 存货编码
            "C": 20,  # 存货名称
            "D": 30,  # 规格型号
            "E": 6,  # 单位
            "F": 10,  # 采购申请数量
            "G": 10,  # 报价
            "H": 10,  # 总价
            "I": 15,  # 备注（品牌）
        }

        for col_letter, width in widths.items():
            sheet.column_dimensions[col_letter].width = width

        # 供应商报价列统一宽度
        for col in range(10, sheet.max_column + 1):
            sheet.column_dimensions[get_column_letter(col)].width = 12
